with three_code as( 
        select  t1.staff_code,
                max(t1.staff_name) as staff_name,
                t1.tail_code
        from jms_dim.dim_lmdm_sys_tail_code t
                 join jms_dim.dim_lmdm_sys_tail_code_staff t1 on t.id=t1.tail_id
        where   t.is_enable='1' and t.is_delete='1'
        group by  t1.staff_code, t1.tail_code 
        )
,deliver_base as (
    select 
      t.waybill_no
     ,max(t.box_num)       as help_send_user_code       --代派code
     ,max(t4.staff_name)  as  help_send_user  --代派name
     ,max(t4.tail_code)  as help_send_three_code   --代派三段码     
     ,max(t.send_user_code) as   send_user_code    --派件人code
     ,max(t.send_user )    as send_user       --派件人
     ,max(t5.tail_code) as send_three_code --派件人三段码
    from jms_dwd.dwd_tab_barscan_deliver_base_dt t  
    left join  three_code t4 on t.box_num=t4.staff_code
    left join  three_code t5 on t.send_user_code=t5.staff_code
        
    where  t.dt between '{{ execution_date | date_add(-30) | cst_ds }}'  AND '{{ execution_date | cst_ds }}'
           and t.box_num is not null  
           and t4.tail_code is not null
   group by t.waybill_no
)

insert overwrite table jms_dm.dm_lmdm_three_segment_monitoring_help_deliver_dt partition ( dt )
select
   t.dt as date_time
  ,max(t.sign_later_scantime ) as sign_later_scantime
  ,t.waybill_no
  ,max(t.input_time) as input_time
  ,max(t.order_source_code) as order_source_code
  ,max(t.order_source_name) as order_source_name
  ,max(t.real_pick_name ) as real_pick_name
  ,max(t.real_pick_code ) as real_pick_code
  ,max(t.receiver_province_id ) as receiver_province_id
  ,max(t.receiver_province_desc ) as receiver_province_desc
  ,max(t.receiver_city_id ) as receiver_city_id
  ,max(t.receiver_city_desc ) as receiver_city_desc
  ,max(t.receiver_area_id ) as receiver_area_id
  ,max(t.receiver_area_desc ) as receiver_area_desc
  ,max(t.receiver_detailed_address   ) as receiver_detailed_address
  ,max(t.deliver_agent_name ) as deliver_agent_name
  ,max(t.deliver_agent_code ) as deliver_agent_code
  ,max(t.deliver_name ) as deliver_name
  ,max(t.deliver_code ) as deliver_code
  ,max(t1.send_user_code) as send_user_code
  ,max(t1.send_user) as send_user
  ,max(t1.send_three_code) as send_three_code
  ,max(t1.help_send_user_code) as help_send_user_code
  ,max(t1.help_send_user) as help_send_user
  ,max(t1.help_send_three_code) as help_send_three_code
  ,max(t.first_code ) as first_code
  ,max(t.second_code ) as second_code
  ,max(t.third_code ) as third_code
  ,max(t.sj_first_code ) assj_first_code
  ,max(t.sj_second_code) as sj_second_code
  ,max(t.sj_third_code ) as sj_third_code
  ,t.dt
from jms_dm.dm_lmdm_three_segment_monitoring_detail_dt t 
join deliver_base t1  on t.waybill_no=t1.waybill_no
where t.dt = '{{ execution_date | cst_ds }}'
group by t.waybill_no,t.dt

 distribute by pmod(hash(rand()), 9);
